#!/usr/bin/env python
# -*- coding: utf-8 -*-

"""
    Forrest Cao   2022.11

"""

import sys, os, time, datetime
import logging
import json
import sqlite3
import utils


def get_course_name(_course):
    if 'course_chinese' == _course:
        return (1,'语文')
    elif 'course_mathematics' == _course:
        return (2,'数学')
    elif 'course_english' == _course:
        return (3,'英语')
    elif 'course_politics' == _course:
        return (4,'道法')
    elif 'course_biology' == _course:
        return (5,'生物')
    elif 'course_history' == _course:
        return (6,'历史')
    elif 'course_geography' == _course:
        return (7,'地理')
    elif 'course_physics' == _course:
        return (8,'物理')
    elif 'course_chemistry' == _course:
        return (9,'化学')
    else:
        return (None,None)
def get_course_field(_cid):
    if _cid == 1:
        return 'course_chinese'
    elif _cid == 2:
        return 'course_mathematics'
    elif _cid == 3:
        return 'course_english'
    elif _cid == 4:
        return 'course_politics'
    elif _cid == 5:
        return 'course_biology'
    elif _cid == 6:
        return 'course_history'
    elif _cid == 7:
        return 'course_geography'
    elif _cid == 8:
        return 'course_physics'
    elif _cid == 9:
        return 'course_chemistry'
    return None

class ConnDb():
    def __init__(self):
        self.conn = None
        self.cursor = None
        self.update_time = time.time()
    def close_db(self):
        self.cursor.close()
        #self.conn.close()
    def begin_transaction(self):
        self.cursor.execute("BEGIN TRANSACTION;")
    def commit_transaction(self):
        self.cursor.execute('COMMIT;')
    def query(self,_sql):
        self.cursor.execute(_sql)
        rs = self.cursor.fetchall()
        return self.pack_db_field(self.cursor.description, rs)
    def exec(self,_sql):
        self.cursor.execute(_sql)
        self.conn.commit() 
    def ping(self):
        sql = '''select now() as systemTime;'''
        rs = self.query(sql)
    def pack_db_field(self, description, rs):
        records = []
        for item in rs:
            rec = {}
            i = 0
            for desp in description:
                rec[desp[0]] = item[i]
                i += 1
            records.append(rec)
        return records

class DBM():
    def __init__(self):
        self.db_filepath = './xqing.db3'
        self.show_sql = True #是否打印sql
        self.conn = ConnDb()
    def conn_db(self):
        if os.path.exists(self.db_filepath) and os.path.isfile(self.db_filepath):
            self.conn.conn = sqlite3.connect(self.db_filepath)
            self.conn.cursor = self.conn.conn.cursor()
    def init_db_conn(self,_number):
        pass
    def getConn(self):
        return self.conn
    def releaseConn(self,_conn):
        pass


class dbm_xqing():
    def __init__(self,_dbm):
        self.dbm = _dbm

    def get_member_id(self,_username):
        sql = '''SELECT member_id FROM member WHERE username='%s';''' % _username
        rs = self.dbm.getConn().query(sql)
        member_id = rs[0]['member_id']
        return member_id

    def get_category_key(self,_cid):
        return 'category_%d' % _cid

    #登录
    def login(self,_username, _pwd):
        username = _username.replace('\'','').replace('"','').replace('`','')
        password = _pwd.replace('\'','').replace('"','').replace('`','')
        dbconn = self.dbm.getConn()

        body = {}
        sql = '''SELECT * FROM member WHERE username='%s';''' % username
        rs = dbconn.query(sql)
        token = utils.reverse_str(username)
        body['token'] = token
        body['is_admin'] = False
        if len(rs) > 0:
            for item in rs:
                pwd = item['password']
                if pwd == password:
                    if item['member_id'] == 1:
                        body['is_admin'] = True
                    return (0,'success',body)
            return (1,'密码错误',body)
        else:
            # insert user
            sql = '''INSERT INTO member(username,password,timestamp) VALUES('%s','%s',%d);''' % (username,password,int(time.time()))
            dbconn.exec(sql)
            return (0,'success',body)

    #查询已登记的考试列表
    def get_examination_records(self,_username):
        member_id = self.get_member_id(_username)
        dbconn = self.dbm.getConn()

        exam_list = []
        sql = '''SELECT exam_id FROM results WHERE member_id=%d;''' % member_id
        rs = dbconn.query(sql)
        for item in rs:
            exam_id = item['exam_id']

            sql = '''SELECT exam_name FROM examination WHERE exam_id=%d;''' % exam_id
            rrs = dbconn.query(sql)
            exam_name = rrs[0]['exam_name']

            sql = '''SELECT COUNT(member_id) FROM results WHERE exam_id=%d;''' % exam_id
            rrss = dbconn.query(sql)
            member_count = rrss[0]['COUNT(member_id)']

            #print(exam_id,exam_name,member_count)

            item = {}
            item['eid'] = exam_id
            item['name'] = exam_name
            item['members'] = member_count
            exam_list.append(item)
        return (0,'success',exam_list)

    #查询考试的详情
    def get_examination_detail(self,_username,_exam_id):
        member_id = self.get_member_id(_username)

        exam_d = {}
        exam_d['category_count'] = 0
        exam_d['category_sum_full_result'] = 0
        cid_key = self.get_category_key(0)
        exam_d[cid_key] = {}
        exam_d[cid_key]['sum_result'] = 0
        exam_d[cid_key]['average_result'] = 0
        exam_d[cid_key]['max_result'] = 0

        exam_detail = {}
        exam_detail['exam_id'] = _exam_id
        exam_detail['title'] = 'title'
        exam_detail['member_count'] = 0
        exam_detail['categorys'] = []
        exam_detail['results'] = []

        category_0 = {}
        category_0['cid'] = 0
        category_0['name'] = '总分'
        #category_sum['average_result'] = 0 #总分的平均分
        #category_sum['max_result'] = 0  #总分的最高分
        category_0['full_result'] = 0 #总分的满分
        exam_detail['categorys'].append(category_0)

        dbconn = self.dbm.getConn()
        sql = '''SELECT * FROM examination WHERE exam_id=%d;''' % _exam_id
        rs = dbconn.query(sql)
        exam_detail['title'] = rs[0]['exam_name']
        for (k,v) in rs[0].items():
            (cid,name) = get_course_name(k)
            if cid != None and v > 0:
                category = {}
                category['cid'] = cid
                category['name'] = name
                category['average_result'] = 0 #此课程的平均分
                category['max_result'] = 0  #此课程的最高分
                category['full_result'] = v
                exam_detail['categorys'].append(category)

                exam_d['category_count'] += 1
                exam_d['category_sum_full_result'] += v
                cid_key = self.get_category_key(cid)
                exam_d[cid_key] = {}
                exam_d[cid_key]['sum_result'] = 0
                exam_d[cid_key]['average_result'] = 0
                exam_d[cid_key]['max_result'] = 0

        #所有科目满分合计
        category_0['full_result'] = exam_d['category_sum_full_result']

        #print('>> ------------------------------------')
        #print('exam_id',exam_detail['exam_id'])
        #print('title',exam_detail['title'])
        #print('categorys',exam_detail['categorys'])
        #print('------------------------------------ <<')

        #得到所有已登记的成绩
        sql = '''SELECT * FROM results WHERE exam_id=%d;''' % _exam_id
        rs = dbconn.query(sql)
        member_list = []
        member_me = None
        for item in rs:
            member = {}
            member['member_id'] = item['member_id']
            member['me'] = 0
            if member['member_id'] == member_id:
                member['me'] = 1
            sum_score = 0
            for (k,score) in item.items():
                #print(k,score)
                (cid,name) = get_course_name(k)
                if cid != None:
                    cid_key = self.get_category_key(cid)
                    member[cid_key] = score
                    sum_score += score
                    #print('member %d. score[ %d : %s : %d ]' % (member['member_id'],cid,name,member[cid_key]))
            member['sum_score'] = sum_score
            member_list.append(member)
            if member['me'] == 1:
                member_me = member
        #print('member_list -> ',member_list)
        member_count = len(member_list)
        #print('member_count -> ',member_count)
        #print('member_me -> ',member_me)

        exam_detail['member_count'] = member_count

        for member in member_list:
            for c in exam_detail['categorys']:
                cid = c['cid']
                cid_key = self.get_category_key(cid)
                if cid == 0:
                    exam_d[cid_key]['sum_result'] += member['sum_score']
                    if member['sum_score'] > exam_d[cid_key]['max_result']:
                        exam_d[cid_key]['max_result'] = member['sum_score']
                    continue
                score = member[cid_key] #此学生此科目分数
                exam_d[cid_key]['sum_result'] += score
                if score > exam_d[cid_key]['max_result']:
                    exam_d[cid_key]['max_result'] = score

        #print('------------------------------------')
        for c in exam_detail['categorys']:
            cid = c['cid']
            #if cid == 0:
            #    continue
            cid_key = self.get_category_key(cid)
            average_result = exam_d[cid_key]['sum_result'] / member_count
            exam_d[cid_key]['average_result'] = round(average_result,1)
            #输出科目统计信息
            #print('Category  %d : %s  full_result[ %d ] members[ %d ] average_result[ %.1f ] max_result[ %.1f ]' %(c['cid'],c['name'],c['full_result'],member_count,exam_d[cid_key]['average_result'],exam_d[cid_key]['max_result']))
        #print('------------------------------------')


        # results -> category 0
        results_category_0 = {}
        results_category_0['category'] = 0
        results_category_0['stat'] = []
        for c in exam_detail['categorys']:
            cid = c['cid']
            cid_key = self.get_category_key(cid)
            item = {}
            item['cid'] = cid
            if cid == 0:
                item['result'] = member_me['sum_score']
                item['full_result'] = c['full_result']
            else:
                item['result'] = member_me[cid_key]
                item['full_result'] = c['full_result']
            results_category_0['stat'].append(item)

        results_category_0['sum_details'] = []
        idx = 0
        for c in exam_detail['categorys']:
            cid = c['cid']
            cid_key = self.get_category_key(cid)
            item = {}
            item['idx'] = idx
            item['cid'] = cid
            if cid == 0:
                item['result'] = member_me['sum_score']
            else:
                item['result'] = member_me[cid_key]
            item['rank'] = 0
            item['average_result'] = exam_d[cid_key]['average_result']
            item['max_result'] = exam_d[cid_key]['max_result']
            results_category_0['sum_details'].append(item)
            idx += 1

        #按总成绩排序
        member_list.sort(key=lambda x:x['sum_score'], reverse=True)

        #所有学生总分明细
        results_category_0['details'] = []
        rank = 1 #得到科目排名
        idx = 1
        for member in member_list:
            item = {}
            item['idx'] = idx
            item['result'] = member['sum_score']
            item['me'] = member['me']
            if item['me'] == 1:
                rank = idx
            results_category_0['details'].append(item)
            idx += 1
        results_category_0['sum_details'][0]['rank'] = rank  #得到自己的总分排名
        #print('results -> category[0]',results_category_0)
        exam_detail['results'].append(results_category_0)

        #print('>> ------------------------------------')
        #print('results',exam_detail['results'])
        #print('------------------------------------ <<')

        '''
        #按总分排序
        member_list.sort(key=lambda x:x['sum_score'], reverse=True)
        #print(member_list)
        rank = 1 #得到自己的总分排名
        idx = 1
        for member in member_list:
            if member['me'] == 1:
                rank = idx
                break
            idx += 1
        results_category_0['details'][0]['rank'] = rank  #得到自己的总分排名
        #print('\nresults_category_0',results_category_0)
        '''

        #单科科目
        for c in exam_detail['categorys']:
            cid = c['cid']
            cid_key = self.get_category_key(cid)
            if cid == 0:
                continue
            results_category = {}
            results_category['category'] = cid
            results_category['stat'] = {}
            results_category['stat']['result'] = member_me[cid_key]
            results_category['stat']['rank'] = 0
            results_category['stat']['average_result'] = exam_d[cid_key]['average_result']
            results_category['stat']['max_result'] = exam_d[cid_key]['max_result']
            results_category['details'] = []
            #按科目成绩排序
            member_list.sort(key=lambda x:x[cid_key], reverse=True)
            #print(member_list)
            rank = 1 #得到科目排名
            idx = 1
            for member in member_list:
                item = {}
                item['idx'] = idx
                item['result'] = member[cid_key]
                item['me'] = member['me']
                if item['me'] == 1:
                    rank = idx
                results_category['details'].append(item)
                idx += 1
            results_category['stat']['rank'] = rank #排名
            #print(results_category)
            exam_detail['results'].append(results_category)

            #更新汇总的科目排名
            for oo in results_category_0['sum_details']:
                if oo['cid'] == cid:
                    oo['rank'] = rank

        #print('>> ------------------------------------')
        #print('results',json.dumps(exam_detail))
        #print('------------------------------------ <<')

        return (0,'success',exam_detail)

        """
        {
            'exam_id': exam_id,
            'title': '人分七年级3班秋季期中考试',
            'categorys': [
                { 'cid':0, 'name':"总分", 'full_result':100 },
                { 'cid':1, 'name':"语文", 'full_result':100 },
                { 'cid':2, 'name':"数学", 'full_result':100 },
                { 'cid':3, 'name':"英语", 'full_result':100 },
                { 'cid':4, 'name':"生物", 'full_result':100 },
                { 'cid':5, 'name':"历史", 'full_result':100 },
                { 'cid':6, 'name':"地理", 'full_result':100 },
                { 'cid':7, 'name':"道法", 'full_result':100 }
            ],
            'results': [
                {
                    'category':0,
                    'stat': [
                        { 'cid':0, 'result':315.6, 'full_result':700 },
                        { 'cid':1, 'result':51.6, 'full_result':100 },
                        { 'cid':2, 'result':52.6, 'full_result':100 },
                        { 'cid':3, 'result':55.7, 'full_result':100 },
                        { 'cid':4, 'result':55.6, 'full_result':100 },
                        { 'cid':5, 'result':54.9, 'full_result':100 },
                        { 'cid':6, 'result':55.6, 'full_result':100 },
                        { 'cid':7, 'result':55.9, 'full_result':100 }
                    ],
                    'sum_details': [
                        { 'idx': 0, 'cid': 0, 'result':255,  'rank': 8,  'average_result': 357.5, 'max_result': 398 },
                        { 'idx': 1, 'cid': 1, 'result':66.5, 'rank': 6,  'average_result': 57.5,  'max_result': 97.5 },
                        { 'idx': 2, 'cid': 2, 'result':67.5, 'rank': 5,  'average_result': 61,    'max_result': 93 },
                        { 'idx': 3, 'cid': 3, 'result':45,   'rank': 3,  'average_result': 65,    'max_result': 88.5 },
                        { 'idx': 4, 'cid': 4, 'result':56.5, 'rank': 11, 'average_result': 58.5,  'max_result': 92.5 },
                        { 'idx': 5, 'cid': 5, 'result':54,   'rank': 21, 'average_result': 63.5,  'max_result': 94 },
                        { 'idx': 6, 'cid': 6, 'result':83,   'rank': 32, 'average_result': 77.3,  'max_result': 96 },
                        { 'idx': 7, 'cid': 7, 'result':75,   'rank': 40, 'average_result': 76.5,  'max_result': 98.5 }
                    ],
                    'details': [
                        { 'idx': 1, 'result':60.5, 'me':0 },
                        { 'idx': 2, 'result':55.5, 'me':1 },
                        { 'idx': 3, 'result':55.5, 'me':0 },
                        { 'idx': 4, 'result':55.5, 'me':0 },
                        { 'idx': 5, 'result':55.5, 'me':0 },
                        { 'idx': 6, 'result':55.5, 'me':0 },
                        { 'idx': 7, 'result':55.5, 'me':0 },
                        { 'idx': 8, 'result':55.5, 'me':0 },
                        { 'idx': 9, 'result':55.5, 'me':1 },
                        { 'idx': 10, 'result':55.5, 'me':0 },
                        { 'idx': 11, 'result':55.5, 'me':0 },
                        { 'idx': 12, 'result':55.5, 'me':0 },
                        { 'idx': 13, 'result':55.5, 'me':0 },
                        { 'idx': 14, 'result':55.5, 'me':0 }
                    ]
                },
                {
                    'category':1,
                    'stat': {
                        'result':60.5, 'rank': 6, 'average_result': 57.5, 'max_result': 78.1
                    },
                    'details': [
                        { 'idx': 1, 'result':60.5, 'me':0 },
                        { 'idx': 2, 'result':55.5, 'me':1 },
                        { 'idx': 3, 'result':55.5, 'me':0 },
                        { 'idx': 4, 'result':55.5, 'me':0 },
                        { 'idx': 5, 'result':55.5, 'me':0 },
                        { 'idx': 6, 'result':55.5, 'me':0 },
                        { 'idx': 7, 'result':55.5, 'me':0 },
                        { 'idx': 8, 'result':55.5, 'me':0 },
                        { 'idx': 9, 'result':55.5, 'me':1 },
                        { 'idx': 10, 'result':55.5, 'me':0 },
                        { 'idx': 11, 'result':55.5, 'me':0 },
                        { 'idx': 12, 'result':55.5, 'me':0 },
                        { 'idx': 13, 'result':55.5, 'me':0 },
                        { 'idx': 14, 'result':55.5, 'me':0 }
                    ]
                },
                {
                    'category':2,
                    'stat': {
                        'result':66.9, 'rank': 8, 'average_result': 57.5, 'max_result': 78.1
                    },
                    'details': [
                        { 'idx': 1, 'result':66.9, 'me':0 },
                        { 'idx': 2, 'result':66.9, 'me':1 },
                        { 'idx': 3, 'result':66.9, 'me':0 },
                        { 'idx': 4, 'result':66.9, 'me':0 },
                        { 'idx': 5, 'result':66.9, 'me':0 },
                        { 'idx': 6, 'result':66.9, 'me':0 },
                        { 'idx': 7, 'result':66.9, 'me':0 },
                        { 'idx': 8, 'result':66.9, 'me':0 },
                        { 'idx': 9, 'result':66.9, 'me':1 },
                        { 'idx': 10, 'result':66.9, 'me':0 },
                        { 'idx': 11, 'result':66.9, 'me':0 },
                        { 'idx': 12, 'result':66.9, 'me':0 },
                        { 'idx': 13, 'result':66.9, 'me':0 },
                        { 'idx': 14, 'result':66.9, 'me':0 }
                    ]
                },
                {
                    'category':3,
                    'stat': {
                        'result':88.1, 'rank': 8, 'average_result': 57.5, 'max_result': 78.1
                    },
                    'details': [
                        { 'idx': 1, 'result':88.1, 'me':0 },
                        { 'idx': 2, 'result':88.1, 'me':1 },
                        { 'idx': 3, 'result':88.1, 'me':0 },
                        { 'idx': 4, 'result':88.1, 'me':0 },
                        { 'idx': 5, 'result':88.1, 'me':0 },
                        { 'idx': 6, 'result':88.1, 'me':0 },
                        { 'idx': 7, 'result':88.1, 'me':0 },
                        { 'idx': 8, 'result':88.1, 'me':0 },
                        { 'idx': 9, 'result':88.1, 'me':1 },
                        { 'idx': 10, 'result':88.1, 'me':0 },
                        { 'idx': 11, 'result':88.1, 'me':0 },
                        { 'idx': 12, 'result':88.1, 'me':0 },
                        { 'idx': 13, 'result':88.1, 'me':0 },
                        { 'idx': 14, 'result':88.1, 'me':0 }
                    ]
                }
            ]
        }
        """

    #验证提交的考试识别编码
    def verify_examination_code(self,_username,_examinationCode):
        member_id = self.get_member_id(_username)
        dbconn = self.dbm.getConn()

        body = {}
        body['exam_id'] = 0
        body['exam_name'] = '---'

        sql = '''SELECT * FROM examination WHERE exam_code=%s;''' % _examinationCode
        rs = dbconn.query(sql)
        if len(rs) > 0:
            body['exam_id'] = rs[0]['exam_id']
            body['exam_name'] = rs[0]['exam_name']
            #为此次考试添加一条登记记录
            sql = '''INSERT OR IGNORE INTO results(member_id,exam_id,timestamp) VALUES(%d,%d,%d);''' % (member_id,body['exam_id'],int(time.time()))
            dbconn.exec(sql)
            return (0,'success',body)
        return (1,'examination code error',body)

    #查询指定的某次考试的成绩
    def get_examination_results(self,_username,_exam_id):
        member_id = self.get_member_id(_username)
        dbconn = self.dbm.getConn()

        exam_results = {}
        exam_results['exam_id'] = _exam_id
        exam_results['exam_name'] = '---'
        exam_results['details'] = []

        sql = '''SELECT * FROM examination WHERE exam_id=%d;''' % _exam_id
        rs = dbconn.query(sql)
        if len(rs) > 0:
            exam_results['exam_name'] = rs[0]['exam_name']
            for (k,v) in rs[0].items():
                (cid,name) = get_course_name(k)
                if cid != None and v > 0:
                    item = {}
                    item['cid'] = cid
                    item['name'] = name
                    item['result'] = 0
                    exam_results['details'].append(item)

            sql = '''SELECT * FROM results WHERE member_id=%d AND exam_id=%d;''' % (member_id,_exam_id)
            rs = dbconn.query(sql)
            for (k,v) in rs[0].items():
                (cid,name) = get_course_name(k)
                if cid != None and v > 0:
                    result = v
                    for item in exam_results['details']:
                        if item['cid'] == cid:
                            item['result'] = result
            #print(exam_results)
            return (0,'success',exam_results)
        return (1,'system exception',exam_results)

        '''
        {
            'exam_id': '1234',
            'exam_name': '人分七年级3班秋季期中考试',
            'details': [
                { 'cid':1, 'name':"语文", 'result': 96 },
                { 'cid':2, 'name':"数学", 'result': 95 },
                { 'cid':3, 'name':"英语", 'result': 94 },
                { 'cid':4, 'name':"生物", 'result': 93 },
                { 'cid':5, 'name':"历史", 'result': 100 },
                { 'cid':6, 'name':"地理", 'result': 91.5 },
                { 'cid':7, 'name':"道法", 'result': 97 }
            ]
        }
        '''

    #提交考试成绩
    def submit_examination_results(self,_username,_exam_id,_results):
        member_id = self.get_member_id(_username)
        dbconn = self.dbm.getConn()

        body = {}
        body['exam_id'] = _exam_id

        sql = '''SELECT * FROM results WHERE member_id=%d AND exam_id=%d;''' % (member_id,_exam_id)
        rs = dbconn.query(sql)
        if len(rs) > 0:
            sql_part = ''
            for item in _results:
                sql_part += '%s=%s,' % (get_course_field(item['cid']),item['result'])
            sql = '''UPDATE results SET %s, timestamp=%d WHERE member_id=%d AND exam_id=%d;''' % (sql_part[:-1],int(time.time()),member_id,_exam_id)
            #print(sql)
            dbconn.exec(sql)
            return (0,'success',body)
        return (1,'system exception',body)

    #删除登记的考试成绩
    def delete_examination_results(self,_username,_exam_id):
        member_id = self.get_member_id(_username)
        dbconn = self.dbm.getConn()

        body = {}
        body['exam_id'] = _exam_id

        sql = '''DELETE FROM results WHERE member_id=%d AND exam_id=%d;''' % (member_id,_exam_id)
        rs = dbconn.exec(sql)
        #print(rs)
        return (0,'success',body)
        # return (1,'system exception',body)


    #管理 - 查询所有考试列表
    def get_examinations(self):
        dbconn = self.dbm.getConn()
        exam_list = []
        sql = '''SELECT * FROM examination;'''
        rs = dbconn.query(sql)
        if len(rs) > 0:
            for item in rs:
                exam = {}
                exam['eid'] = item['exam_id']
                exam['name'] = item['exam_name']
                exam['code'] = item['exam_code']

                sql = '''SELECT COUNT(member_id) FROM results WHERE exam_id=%d;''' % item['exam_id']
                rss = dbconn.query(sql)
                member_count = rss[0]['COUNT(member_id)']
                
                exam['members'] = member_count
                exam_list.append(exam)
            return (0,'success',exam_list)
        return (1,'system exception',exam_list)
        '''
        exam_list = [
            { "eid": 1, "name": "人分七年级3班期中考试", "code":123456, "members":100 },
            ...
        ]
        '''

    #管理 - 查询学校列表
    def get_schools(self):
        dbconn = self.dbm.getConn()
        school_list = []
        sql = '''SELECT * FROM school;'''
        rs = dbconn.query(sql)
        if len(rs) > 0:
            for item in rs:
                school = {}
                school['sid'] = item['school_id']
                school['name'] = item['school_name']
                school_list.append(school)
            return (0,'success',school_list)
        return (1,'system exception',school_list)
        '''
        school_list = [
            { "sid": 1, "name": "人分学校" },
            { "cid": 2, "name": "人大附中本部" },
            ...
        ]
        '''

    #管理 - 查询所有科目列表
    def get_courses(self):
        dbconn = self.dbm.getConn()
        course_list = []
        sql = '''SELECT * FROM examination WHERE exam_id=1;'''
        rs = dbconn.query(sql)
        if len(rs) > 0:
            for (k,v) in rs[0].items():
                (cid,name) = get_course_name(k)
                if cid != None:
                    course = {}
                    course['cid'] = cid
                    course['name'] = name
                    course_list.append(course)
            return (0,'success',course_list)
        return (1,'system exception',course_list)
        '''
        course_list = [
            { "cid": 1, "name": "语文" },
            { "cid": 2, "name": "数学" },
            ...
            { "cid": 9, "name": "化学" }
        ]
        '''

    #管理 - 查询考试信息
    def examination_detail(self,_exam_id):
        dbconn = self.dbm.getConn()
        sql = '''SELECT * FROM examination WHERE exam_id=%d;''' % _exam_id
        rs = dbconn.query(sql)
        exam_detail = {}
        if len(rs) > 0:
            exam_detail['eid'] = _exam_id
            exam_detail['name'] = rs[0]['exam_name']
            exam_detail['sid'] = rs[0]['school_id']
            exam_detail['courses'] = []
            for (k,v) in rs[0].items():
                (cid,name) = get_course_name(k)
                if cid != None:
                    course = {}
                    course['cid'] = cid
                    course['name'] = name
                    course['full_result'] = v
                    exam_detail['courses'].append(course)
            return (0,'success',exam_detail)
        return (1,'system exception',exam_detail)
        '''
        exam_detail = {
            "eid": 1,
            "name": "abc考试",
            "sid": 1,
            "courses": [
                { "cid": 2, "name": "数学" },
                { "cid": 9, "name": "化学" }
            ]
        }
        '''

    #管理 - 增加考试 / 修改考试
    def add_examination(self,_exam):
        dbconn = self.dbm.getConn()
        exam_id = int(_exam['eid'])
        if exam_id == 0:
            #新增考试
            sql = '''select exam_id from examination ORDER BY exam_id DESC LIMIT 0,1;'''
            rs = dbconn.query(sql)
            if len(rs) > 0:
                max_exam_id = rs[0]['exam_id']
                max_exam_id += 1
                exam_code = (time.time()*1000000 % 1000000)
                if exam_code <= 99999:
                    exam_code += 100000
                sql = '''INSERT OR IGNORE INTO examination(exam_id,exam_name,exam_code,school_id) VALUES(%d,'%s',%d,%d);''' % (max_exam_id,_exam['name'],exam_code,_exam['sid'])
                print(sql)
                dbconn.exec(sql)
                sql_part = ''
                for item in _exam['courses']:
                    sql_part += '%s=%s,' % (get_course_field(item['cid']),item['full_result'])
                sql = '''UPDATE examination SET %s WHERE exam_id=%d;''' % (sql_part[:-1],max_exam_id)
                print(sql)
                dbconn.exec(sql)
                return (0,'success','')
        else:
            #修改考试
            sql_part = ''
            for item in _exam['courses']:
                sql_part += '%s=%s,' % (get_course_field(item['cid']),item['full_result'])
            sql = '''UPDATE examination SET %s, exam_name='%s', school_id=%d WHERE exam_id=%d;''' % (sql_part[:-1],_exam['name'],_exam['sid'],exam_id)
            print(sql)
            dbconn.exec(sql)
            return (0,'success','')
        return (1,'system exception','')
        '''
        examination: {
            "eid": 0,
            "name": "输入考试名称",
            "sid": 1,
            "courses": [
                { "cid": 2, "name": "数学", "full_result": 100 },
                { "cid": 9, "name": "化学", "full_result": 100 }
            ]
        }
        '''

    #管理 - 删除考试
    def del_examination(self,_exam_id):
        # 删除考试下登记的成绩
        # 删除考试
        dbconn = self.dbm.getConn()
        sql = '''DELETE FROM results WHERE exam_id=%d;''' % _exam_id
        rs = dbconn.exec(sql)
        print(rs)
        sql = '''DELETE FROM examination WHERE exam_id=%d;''' % _exam_id
        rs = dbconn.exec(sql)
        print(rs)
        return (0,'success','')

    #管理 - 查询用户密码
    def get_member(self,_username):
        dbconn = self.dbm.getConn()
        sql = '''SELECT * FROM member WHERE username='%s';''' % _username
        rs = dbconn.query(sql)
        print(rs)
        body = {}
        body['username'] = _username
        if len(rs) > 0:
            body['member_id'] = rs[0]['member_id']
            body['password'] = rs[0]['password']
            return (0,'success',body)
        return (1,'system exception',body)

